package shuai.test;

import java.math.BigDecimal;
import java.util.Calendar;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import com.sun.org.apache.bcel.internal.generic.NEW;
import com.sun.xml.internal.ws.api.Cancelable;

import javafx.scene.shape.Circle;
import shuai.model.Books;
import shuai.model.Orders;
import shuai.model.Users;

public class Demo {
	Session session=null;
	@Before
	public void initSession(){
		Configuration configuration =new Configuration().configure();
		SessionFactory sessionFactory=configuration.buildSessionFactory();
		session=sessionFactory.openSession();
	}
	
	@Test
	public void demo(){
		Orders orders=(Orders) session.get(Orders.class, new BigDecimal(2));
		session.close();
		System.out.println(orders.getOrderdate());
	}
	
	@Test
	public void Demo1(){
		String hql="select o.users from Orders o where o.id=2";
		Query query=session.createQuery(hql);
		List<Users> users=query.list();
		session.close();
		Users user=users.get(0);
		System.out.println("下单人是："+user.getName());
	}
	
	@Test
	public void Demo2(){
		//String hql="select u from Users u where u in (select o.users from Orders o)";
		String hql="select distinct u from Users u inner join u.orderses";
		Query query=session.createQuery(hql);
		List<Users> users=query.list();
		System.out.println("下过单的人有：");
		for (Users u:users){
			System.out.println(u.getName());
		}		
	}
	
	@Test
	public void Demo3(){
		String hql="select distinct u from Users u inner join u.orderses";
		List<Users> users=session.createQuery(hql).list();
		for(Users u:users){
			System.out.println("用户"+u.getName()+"所下的单为");
			for(Orders orders :u.getOrderses()){
				System.out.println(orders.getId()+","+orders.getTotalprice());
			}
		}
	}
	
	@Test
	public void Demo4(){
		Query query=session.getNamedQuery("myNamedQuery");
		query.setString("title", "%哼%");
		List<Books> books=query.list();
		for(Books b:books){
			System.out.println(b.getTitle());
		}
	}
	@Test
	public void Demo5(){
		String hql="select * from books";
		List<Books> books=session.createSQLQuery(hql).addEntity(Books.class).list();
		for(Books b:books){
			System.out.println(b.getId()+b.getTitle());
		}
		
		/*List<Object[]> list=session.createSQLQuery(hql).list();
		for(Object[] objects :list){
			System.out.println("id"+objects[0]+",title"+objects[1]);
		}*/
	}
	@Test
	public void Demo6(){
		List<Object[]> list=session.createSQLQuery("select {u.*},{o.*} from users u,orders o where u.id=o.userid")
				.addEntity("u",Users.class).addEntity("o",Orders.class).list();
		for (Object[] o:list) {
			Users u=(Users) o[0];
			Orders orders =(Orders) o[1];
			System.out.println("用户："+u.getName()+"的订单金额:"+orders.getTotalprice());
		}		
	}
	
	@Test
	public void Demo7(){
		List<Object[]> list=session.getNamedQuery("mySqlQuery").list();
		for (Object[] o:list) {
			Users u=(Users) o[0];
			Orders orders =(Orders) o[1];
			System.out.println("用户："+u.getName()+"的订单金额:"+orders.getTotalprice());
		}
	}
	
	@Test
	public void Demo8(){
		Criteria cri=session.createCriteria(Users.class);
		List<Users> users =cri.list();
		for(Users u:users){
			System.out.println(u.getName());
		}
	}
	
	@Test
	public void Demo9(){
		Criteria cri=session.createCriteria(Orders.class);
		List<Orders> list=cri.add(Restrictions.gt("totalprice", new BigDecimal(300))).list();
		for (Orders o:list){
			System.out.println(o.getId()+","+o.getTotalprice());
		}
	}
	
	@Test
	public void Demo10(){
		Criteria cri=session.createCriteria(Orders.class);
		Calendar calendar=Calendar.getInstance();
		calendar.set(2017,2,9);
		List<Orders> list=cri.add(Restrictions.gt("totalprice", new BigDecimal(300))).add(Restrictions.gt("orderdate", calendar.getTime())).list();
		for (Orders o:list){
			System.out.println(o.getId()+","+o.getTotalprice());
		}
	}
	
	
	@Test
	//降序排列
	public void Demo11(){
		Criteria criteria=session.createCriteria(Users.class);
		List<Users> list=criteria.addOrder(Order.desc("loginid")).list();
		for(Users u:list){
			System.out.println(u.getName()+","+u.getLoginid());
		}
	}
	
	@Test
	public void Demo12(){
		Criteria criteria=session.createCriteria(Books.class);
		List<Books> books=criteria.setFirstResult(1).setMaxResults(2).list();
		for(Books b:books){
			System.out.println(b.getTitle());
		}
	}
	
	
	@Test
	public void Demo13(){
		Criteria criteria=session.createCriteria(Orders.class);
		criteria.setProjection(Projections.avg("totalprice"));
		Double total=(Double) criteria.uniqueResult();
		System.out.println("平均价格："+total);
	}
	
	@Test
	public void Demo14(){
		Criteria criteria=session.createCriteria(Orders.class);
		Users users =new Users();
		users.setId(new BigDecimal(2));
		criteria.add(Restrictions.eq("users", users));
		List<Orders> list=criteria.list();
		for(Orders o:list){
			System.out.println(o.getUsers().getName()+","+o.getTotalprice());
		}
	}
	
	
	@Test
	//使用createCriteria方法在实体间连接查询
	public void Demo15(){
		Criteria c_orders=session.createCriteria(Orders.class);
		Criteria c_users=c_orders.createCriteria("users");
		c_orders.add(Restrictions.gt("totalprice", new BigDecimal(300)));
		c_users.add(Restrictions.eq("id", new BigDecimal(2)));
		List<Orders> list=c_orders.list();
		for(Orders orders : list){
			System.out.println("客户名称:"+orders.getUsers().getName()+"\n订单总价:"+orders.getTotalprice());
		}
	}
	
	@After
	public void destorySession(){
		session.close();
	}
}
